<?php

class sqlFunc extends AppLog
{
	var $tables = array();
	var $fields = array();
	var $where = array();
	var $order = array();
	var $group = array();
	var $values = array();
	var $types = array();
	var $options = array();
	
	var $fieldsSql = "";
	var $tablesSql = "";
	var $whereSql = "";
	var $orderbySql = "";
	var $groupbySql = "";

	var $dateStart = '1970-01-01';
	var $dateEnd = '2999-12-31';

	var $data = array();

	function dbResult( &$ary, $field, $type = '' )
	{
		if ( isset($ary[ strtoupper( $field ) ]) )	$field = strtoupper( $field );
		if ( $type != '' ) return $this->_dataType( $ary[ $field ], $type );
		return $ary[ $field ];
	}
	

	/**
		Insert
			param 0 = string, date, time, number
			param 1 = noempty, empty, empty_null
			param 2 = upper,lower
			param 3 = value default

		example:
			funcSql->tables[] = "cli_clienti"
			funcSql->fields["cli_cliente_id"] = "number,noempty,upper,0";
			funcSql->fields["cli_nome"] = "string,empty,upper,";
			$sql = funcSql->mkInsert();

		Select
			fields
				param 0 = string, date, time, number
				param 1 = upper
				param 2 = [alias]
				
			tables
				param 0 = join, left join, right join
				param 1 = [id join]
				param 2 = [alias]
				param 3 = [table join]
				
			where
				param 0 = field, {data}
						  field = campo database
						  {data} = array data
				param 1 = [alias]
				param 2 = and,or
				param 3 = string, date, time, number, db2date
				param 4 = empty, noempty
				param 5 = upper
				param 6 = =, >, <, >=, <=, <>, like, is null, is not null, exists

		example:
			$_fld[""]
			funcSql->tables[] = "cli_clienti"
			funcSql->fields[] = "cli_cliente_id"
			funcSql->where["cli_cliente_id"] = "and,number,noempty,,=";
			funcSql->where["cli_nome"] = "and,string,,upper,like";
			$sql = funcSql->mkSelect();
	 */

	function _init()
	{
		$this->varReset();
	}
	
	function varReset()
	{
		$this->tables = array();
		$this->fields = array();
		$this->where = array();
		$this->orderby = array();
		$this->order = array();
		$this->group = array();
		$this->values = array();
		$this->types = array();
		$this->options = array();
		
		$this->fieldsSql = "";
		$this->tablesSql = "";
		$this->orderbySql = "";
		$this->whereSql = "";
		$this->groupbySql = "";
	}

	function _table( $par )
	{
		$this->tablesSql = $par;
	}

	function _insField( $fld, $val = '' , $typ, $opt, $def = '' )
	{
		array_push( $this->fields, $fld );
		array_push( $this->values, $val );
		array_push( $this->types , $typ );
		array_push( $this->options , $opt );
		if ( $def != '' ) array_push( $this->defaults , $def );
	}

	function _makeInsert()
	{
		$field = '';
		$value = '';

		while ( $this->fields )
		{
			$ctrl = 1;
			$fld = array_shift( $this->fields );
			$val = array_shift( $this->values );
			$option = array_shift( $this->options );
			$type = array_shift( $this->types );

			if ( preg_match( "/empty no insert/i", $option ) && strlen( $val ) == 0 ) $ctrl = 0;

			if ( $ctrl )
			{
				if ( $field ) $field.= ",";
				$field.= $fld;
		
				if ( $value ) $value.= ",";

				if ( preg_match( "/empty default insert/i", $option ) && strlen( $val ) == 0 ) $val = array_shift( $defaults );

				if ( preg_match( "/empty null insert/i", $option ) && strlen( $val ) == 0 ) $type = 'NULL';

				if ( preg_match( "/empty dete end insert/i", $option ) && strlen( $val ) == 0 ) $type = 'DATE_END';

				$value.= $this->_dataType($val, $type);
			}
		}
		
		return "INSERT INTO ".$this->tablesSql." ($field) VALUES ($value)";
	}

	function _makeUpdate( $where = '' )
	{
		$field = '';
		$value = '';
		
		while ( $this->fields )
		{
			$ctrl = 1;
			$fld = array_shift( $this->fields );
			$val = array_shift( $this->values );
			$option = array_shift( $this->options );
			$type = array_shift( $this->types );

			//if ( ! defined( $val ) ) $val = '';
			
			if ( preg_match( "/empty no insert/i", $option ) && strlen( $val ) == 0 )	$ctrl = 0;

			if ( $ctrl )
			{
				if ( $value ) $value.= ',';
				$value.= $fld;

				if ( preg_match( "/empty default insert/i", $option ) && strlen( $val ) == 0 ) $val = array_shift( $defaults );
			
				if ( preg_match( "/empty null insert/i", $option ) && strlen( $val ) == 0 ) $type = "NULL";
		
				if ( preg_match( "/empty date end insert/i", $option ) && strlen( $val ) == 0 ) $type = "DATE_END";
				
				$value.= "=".$this->_dataType($val, $type);
			}
		}

		if ( $where ) 
		{
			$where = "WHERE $where";
		}
		else
		{
			$where = '';
		}
		
		return "UPDATE ".$this->tablesSql." SET $value $where";
	}

	function _dataType( $value, $type)
	{
		if ( $type == "string" || $type == 'checked' || $type == 'day' )
		{
			$value = str_replace( "'", "''", $value ) ;
			return "'$value'";
		}

		if ( $type == "text" )
		{
			$value = str_replace( "'", "''", $value ) ;
			return "'$value'";
		}

		if ( $type == "date" )
		{
			$valTmp = split( "/", $value);
			return "'".$valTmp[2]."-".$valTmp[1]."-".$valTmp[0]."'";
		}

		if ( $type == "empty date start" )
		{
			if ( $value == '' ) return "'".$this->dateStart."'";
			
			$valTmp = split( "/",$value);
			return "'".$valTmp[2]."-".$valTmp[1]."-".$valTmp[0]."'";
		}
		
		if ( $type == "empty date end" )
		{
			if ( $value == '' ) return "'".$this->dateEnd."'";
			
			$valTmp = split( "/",$value);
			return "'".$valTmp[2]."-".$valTmp[1]."-".$valTmp[0]."'";
		}

		if ( $type == "empty date current" )
		{
			if ( $value == '' ) return "'".FUNZIONI::getDateDb()."'";
			
			$valTmp = split( '/',$value);
			return "'".$valTmp[2]."-".$valTmp[1]."-".$valTmp[0]."'";
		}

		if ( $type == "db2text" )
		{
			return $value;
		}

		if ( $type == "db2date" )
		{
			if ( $value == $this->dateEnd || $value == '' ) return '';
			
			$valTmp = split( "-",$value);
			return sprintf('%02d/%02d/%04d/',$valTmp[2],$valTmp[1],$valTmp[0]);
		}

		if ( $type == 'ts2date' )
		{
			if ( $value == $this->dateEnd || $value == '' ) return '';
			
			$valSep = split( ' ', $value );
			if ( int($valSep[2]) == 0 ) return '';
			$valTmp = split( '-', $valSep[0] );
			$val = sprintf('%02d/%02d/%04d/',$valTmp[2],$valTmp[1],$valTmp[0]);
			$val.= $valSep[1];
			return $val;
		}

		if ( $type == "currency" )
		{
			return _round( _currency2db( $value ), 2 );
		}
		
		if ( $type == "db2currency" )
		{
			return _db2currency( $value );
		}
		
		if ( $type == "numeric" )
		{
			return $value;
		}

		if ( $type == "time" )
		{
			return "'$value'";
		}

		if ( $type == "db2time" )
		{
			$valTmp = split( ":" , $value );
			return sprintf('%02d:%02d',$valTmp[0],$valTmp[1]);
		}

		if ( $type == 'NULL' ) return 'NULL';

		if ( $type == 'DATE_END' ) return "'".$this->dateEnd."'";

		return $value;
	}

	function _round( $number, $precision )
	{
		$mol = 10 ^ $precision;
		$dif = .49 / $mol;
		return intval(($number + $dif) * $mol) / $mol;
	}

	function _currency2db( $number )
	{
		$number = str_replace( '.', '', $number );
		$number = str_replace( ',', '.', $number );
		return $number;
	}

	function _db2currency( $number )
	{
		return $number;
		/*$string = "$number";
		$pun = index($string,'.');
		$int = '';
		$dec = '';
		if ( $pun > -1 )
		{
			$int = substr($string, 0, $pun);
			$dec = substr($string, $pun + 1);
		}
		else
		{
			$int = $string;
			$dec = "00";
		}
		
		$cntInt = strlen($int);
		$grpInt = '                   ';
		$cntGrp = strlen($grpInt);
		while( 1 )
		{
			$cntInt -= 3;
			$cntGrp -= 3;
			if ( $cntInt <= 0 )
			{
				$cntInt += 3;
				substr($grpInt,$cntGrp, 3) = substr($int,0,$cntInt);
				break;
			}
			substr($grpInt,$cntGrp, 3) = substr($int,$cntInt,3);
			$cntGrp--;
			substr($grpInt,$cntGrp, 1) = ".";
		}
		$grpInt = str_replace( ' ', '', $grpInt );
		return "$grpInt,$dec";*/
	}

	function _dataEnd()
	{
		return $dateEnd;
	}

	function mkSelect()
	{
		$_select = "";
		$_from = "";
		$_where = "";
		$_orderby = "";
		$_groupby = "";

		reset($this->tables);
		while (list(, $tmp) = each ($this->tables))
		{
			if ( $_from != "" ) $_from .= ", ";
			$_from .= $tmp;
		}
		$_from .= $this->tablesSql;
		
		reset($this->fields);
		while (list(, $tmp) = each ($this->fields))
		{
			if ( $_select != "" ) $_select .= ", ";
			$_select .= $tmp;
		}
		$_select .= $this->fieldsSql;
		
		reset($this->order);
		while (list(, $tmp) = each ($this->order))
		{
			if ( "$_orderby" != "" ) $_orderby .= ", ";
			$_orderby .= $tmp;
		}
		$_orderby .= $this->orderbySql;
		if ( "$_orderby" != "") $_orderby = " ORDER BY ".$_orderby;

		reset($this->group);
		while (list(, $tmp) = each ($this->group))
		{
			if ( "$_groupby" != "" ) $_groupby .= ", ";
			$_groupby .= $tmp;
		}
		$_groupby .= $this->groupbySql;
		if ( "$_groupby" != "") $_groupby = " GROUP BY ".$_groupby;

		$_where = $this->___where();
		if ( $this->whereSql != "" && $_where != "" ) $_where.= " AND ";
		$_where.= $this->whereSql;
		if ( $_where != "" ) $_where = " WHERE ".$_where;
		
		return "SELECT $_select FROM $_from $_where $_groupby $_orderby";
	}

	function mkSelectDistinct()
	{
		$_select = "";
		$_from = "";
		$_where = "";
		$_orderby = "";
		$_groupby = "";

		reset($this->tables);
		while (list(, $tmp) = each ($this->tables))
		{
			if ( $_from != "" ) $_from .= ", ";
			$_from .= $tmp;
		}
		$_from .= $this->tablesSql;
		
		reset($this->fields);
		while (list(, $tmp) = each ($this->fields))
		{
			if ( $_select != "" ) $_select .= ", ";
			$_select .= $tmp;
		}
		$_select .= $this->fieldsSql;
		
		reset($this->order);
		while (list(, $tmp) = each ($this->order))
		{
			if ( "$_orderby" != "" ) $_orderby .= ", ";
			$_orderby .= $tmp;
		}
		$_orderby .= $this->orderbySql;
		if ( "$_orderby" != "") $_orderby = " ORDER BY ".$_orderby;

		reset($this->group);
		while (list(, $tmp) = each ($this->group))
		{
			if ( "$_groupby" != "" ) $_groupby .= ", ";
			$_groupby .= $tmp;
		}
		$_groupby .= $this->groupbySql;
		if ( "$_groupby" != "") $_groupby = " GROUP BY ".$_groupby;
		
		$_where = $this->___where();
		if ( $this->whereSql != "" && $_where != "" ) $_where.= " AND ";
		$_where.= $this->whereSql;
		if ( $_where != "" ) $_where = " WHERE ".$_where;

		return "SELECT DISTINCT $_select FROM $_from $_where $_groupby $_orderby";
	}

	function mkUpdate( $where = "" )
	{
		$_set = "";
		$_from = "";
		$_where = "";

		reset($this->tables);
		while (list(, $tmp) = each ($this->tables))
		{
			if ( $_from != "" ) $_from .= ", ";
			$_from .= $tmp;
		}

		$_set = $this->___set();

		if ( $where != "" )
		{
			$_where = " $where ";
		}
		else
		{
			$_where = $this->___where();
		}

		return "UPDATE $_from SET $_set WHERE $_where";
	}

	function mkDelete()
	{
		$_set = "";
		$_from = "";
		$_where = "";

		reset($this->tables);
		while (list(, $tmp) = each ($this->tables))
		{
			if ( $_from != "" ) $_from .= ", ";
			$_from .= $tmp;
		}

		$_where = $this->___where();
		if ( $this->whereSql != "" && $_where != "" ) $_where.= " AND ";
		$_where.= $this->whereSql;

		return "DELETE FROM $_from WHERE $_where";
	}

/*	function mkInsert()
	{
		$_field = "";
		$_from = "";
		$_value = "";

		reset($this->tables);
		while (list(, $tmp) = each ($this->tables))
		{
			if ( $_from != "" ) $_from .= ", ";
			$_from .= $tmp;
		}

		$this->___values(&$_field, &$_value);

		return "INSERT INTO $_from ($_field) VALUES ($_value)";
	}
*/
	function ___values($_fld, $_val)
	{
		reset($this->data);
		while (list($key_d, $val_d) = each ($this->data))
		{
			reset($this->fields);
			while (list($key_w, $val_w) = each ($this->fields))
			{	
				if ( $key_w == $key_d )
				{
					$_param = split(",", $val_w);

					if ( $_param[1] == "empty" )
					{
						if ( "$val_d" != "" )
						{
							$_value = $this->_type_format($_param[0], $val_d);
						}
						else
						{
							$_value = "NULL";
						}
					}
					elseif ( $_param[1] == "noempty" ) 
					{
						$_value = $this->_type_format($_param[0], $val_d);
					}
					elseif ( $val_d != "" )
					{
						$_value = $this->_type_format($_param[0], $val_d);
					}
					elseif ( $_param[3] != "" )
					{
						$_value = $this->_type_format($_param[3], $val_d);
					}

					if ( $_param[2] == "upper" ) $_value = strtoupper( $_value );

					if ( "$_value" != "" )
					{
						if ( $_fld != "" ) 
						{
							$_fld .= ", ";
							$_val .= ", ";
						}

						$_fld .= $key_d;
						$_val .= $_value;
					}
					break 1;
				}
			}
			$key_d = "";
			$val_d = "";
			$_value = "";
		}
	}

	function ___set()
	{
		$_set = "";

		reset($this->data);
		while (list($key_d, $val_d) = each ($this->data))
		{
			reset($this->fields);
			while (list($key_w, $val_w) = each ($this->fields))
			{	
				if ( $key_w == $key_d )
				{
					$_param = split(",", $val_w);

					if ( $_param[1] == "empty" )
					{
						if ( "$val_d" != "" )
						{
							$_value = $this->_type_format($_param[0], $val_d);
						}
						else
						{
							$_value = "NULL";
						}
					}
					elseif ( $_param[1] == "noempty" ) 
					{
						if ( "$val_d" != "" )
						{
							$_value = $this->_type_format($_param[0], $val_d);
						}
						else
						{
							$_value = $this->_type_format($_param[0], $_param[3]);
						}
					}
					elseif ( $val_d != "" )
					{
						$_value = $this->_type_format($_param[0], $val_d);
					}
					else
					{
						$_value = "";
					}

					if ( $_param[2] == "upper" ) $_value = strtoupper( $_value );


					if ( "$_value" != "" )
					{
						if ( $_set != "" ) $_set .= ", ";

						$_set .= "$key_d = $_value";
					}
					break 1;
				}
			}
		}
		return $_set;
	}

	function ___where()
	{
		$_where = "";
		
		reset($this->where);
		while (list($key_w, $val_w) = each ($this->where))
		{
			$_value = "";
			$_ctrl = true;
			$_param = split(",", $val_w);

			if ( preg_match("/\{data\}/", $_param[0]) )
			{
				reset($this->data);
				while (list($key_d, $val_d) = each ($this->data))
				{
					if ( $key_d == $key_w )
					{
						$_value = $val_d;
						break;
					}
				}		
			}
			else
			{
				$_value = $_param[0];
			}
			
			if ( preg_match("/^empty/", $_param[4]) )
			{
				if ( "$_value" == "" ) $_ctrl = false;
			}
			
			if ( $_ctrl )
			{
				if ( preg_match("/^and/", $_param[2]) || preg_match("/^or/", $_param[2]) )
				{
					if ( $_where != "" ) $_where.= " ".strtoupper($_param[2])." ";
				}

				if ( $_param[1] != "" ) $key_w = $_param[1].".$key_w";

				if ( preg_match("/upper/", $_param[5]) )
				{
					$_value = strtoupper( $_value );
					$key_w = "UPPER( $key_w )";
				}
								
				$_value = $this->_type_format( $_param[3], $_value, $_param[6] );
				$_where.= "$key_w $_param[6] $_value";
			}
		}
		
		return $_where;
	}

	function _type_format($type, $value, $cond = "")
	{
		if ( strtoupper($value) == "NULL" )	return "NULL"; 

		switch ( $type )
		{
			case "string":
				$val = str_replace("'", "\'", $value);
				if ( $cond == "like" ) 
					return "'%".$val."%'";
				else
					return "'".$val."'";
				break;

			case "string upper":
				$val = strtoupper( str_replace( "'", "\'", $value ) );
				if ( $cond == "like" ) 
					return "'%".$val."%'";
				else
					return "'".$val."'";
				break;

			case "date":
				if ( $value == "" )	return "''";
				
				if ( preg_match("/\-/", $value) )
				{
					if ( $cond == "like" ) 
						return "'%$value%'";
					else	
						return "'$value'";
				}
				else
				{
					$tdate = split("/", $value);
					if ( $cond == "like" ) 
						return "'%".$tdate[2]."-".$tdate[1]."-".$tdate[0]."%'";
					else	
						return "'".$tdate[2]."-".$tdate[1]."-".$tdate[0]."'";
				}
				break;

			case "db2date":
				if ( $value == "" )	return "";

				$tdate = split("-", $value);
				return $tdate[2]."/".$tdate[1]."/".$tdate[0];
				break;

			case "time":
				if ( $cond == "like" ) 
					return "'%".$value."%'";
				else
					return "'".$value."'";
				break;

			case "db timestamp":
				return "('now'::text)::timestamp";
				break;

			case "currency":
			case "currency2db":
			case "percentage2db":
				if ( preg_match("/,/" , $value) )
				{
					$value = str_replace(".","",$value);
					$value = str_replace(",",".",$value);
				}
				if ( $cond == "like" ) 
					return "'%".$value."%'";
				else
					return $value;
				break;

			case "db4currency":
				return "&euro; ".number_format($value, 2, '.', '');
				break;

			case "db2currency":
				return number_format($value, 2, '.', '');
				break;
				
			case "db2percentage":
				$value = str_replace("." , "," ,$value);				
				return $value;
				break;

			case "boolean":
				if ( $value == "on" || $value == true )
					return "S";
				else
					return "N";
				break;
				
			case "number":
				//$value = str_replace(".","",$value);
				$value = str_replace(",",".",$value);
				if ( $cond == "like" ) 
					return "'%".$value."%'";
				else
					return $value;
				break;
				
			case "db2number":
				$value = str_replace(".",",",$value);
				return $value;
				break;
				
			default:
				if ( $cond == "like" ) 
					return "'%".$value."%'";
				else
					return $value;
				break;
		}
	}
}

?>
